package com.yvan.serverless.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
import com.yvan.dynamic.datasource.DynamicRoutingDataSource;
import com.yvan.platform.StringUtils;
import com.yvan.serverless.jdbc.util.DaoExecuterDeleteUtils;
import com.yvan.serverless.jdbc.util.DaoExecuterInsertUtils;
import com.yvan.serverless.jdbc.util.DaoExecuterUpdateUtils;
import com.yvan.serverless.utils.ReflectUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import javax.sql.CommonDataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class DaoExecuter {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private String dbType = null;
    private String tableName = null;

    DaoExecuter(SqlSession sqlSession) {
        try {
            conn = sqlSession.getConnection();
            dbType = getDbType(sqlSession.getConfiguration().getEnvironment().getDataSource());
        } catch (Exception e) {
            log.error(e.getMessage());
            closeAll();
        }
    }

    private int execute(String sql, List<DaoParam> params) throws SQLException {
        setAutoCommit(false);
        // 调用SQL
        pstmt = conn.prepareStatement(sql);
        // 参数赋值
        setParam(params);
        // 执行
        int rz = pstmt.executeUpdate();
//        commit();
        return rz;
    }

    @SneakyThrows
    public int insertAny(String tableName, Map<String, Object> fieldAndValue) {
        int rz = 0;
        try {
            this.tableName = tableName;
            List<Map<String, Object>> fieldAndValueList = Lists.newArrayList();
            fieldAndValueList.add(fieldAndValue);
            String sql = DaoExecuterInsertUtils.getInsertAnySql(tableName, fieldAndValueList, dbType);
            List<DaoParam> values = DaoExecuterInsertUtils.getInsertAnyParams(fieldAndValueList);
            rz = execute(sql, values);
        } finally {
            closeAll();
        }
        return rz;
    }

    public int insertAnyBatch(String tableName, List<Map<String, Object>> fieldAndValueList) {
        int rz = 0;
        try {
            this.tableName = tableName;
            String sql = DaoExecuterInsertUtils.getInsertAnySql(tableName, fieldAndValueList, dbType);
            List<DaoParam> values = DaoExecuterInsertUtils.getInsertAnyParams(fieldAndValueList);
            rz = execute(sql, values);
        } catch (Exception e) {
//            rollback();
//            logger.error(e.getMessage());
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return rz;
    }

    public int updateAny(String tableName, Map<String, Object> sets, Map<String, Object> wheres) {
        int rz = 0;
        try {
            this.tableName = tableName;
            String sql = DaoExecuterUpdateUtils.getUpdateAnySql(tableName, sets, wheres);
            List<DaoParam> values = DaoExecuterUpdateUtils.getUpdateAnyParams(sets, wheres);
            rz = execute(sql, values);
        } catch (Exception e) {
//            rollback();
//            logger.error(e.getMessage());
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return rz;
    }

    public int deleteAny(String tableName, Map<String, Object> wheres) {
        int rz = 0;
        try {
            this.tableName = tableName;
            String sql = DaoExecuterDeleteUtils.getDeleteAnySql(tableName, wheres);
            List<DaoParam> values = DaoExecuterDeleteUtils.getDeleteAnyParams(wheres);
            rz = execute(sql, values);
        } catch (Exception e) {
//            rollback();
//            logger.error(e.getMessage());
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return rz;
    }

    public int deleteByIds(String tableName, String idField, List<Object> ids) {
        int rz = 0;
        try {
            this.tableName = tableName;
            String sql = DaoExecuterDeleteUtils.getDeleteByIdsSql(tableName, idField, ids);
            List<DaoParam> values = DaoExecuterDeleteUtils.getDeleteByIdsParams(idField, ids);
            rz = execute(sql, values);
        } catch (Exception e) {
//            rollback();
//            logger.error(e.getMessage());
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return rz;
    }

    private void setParam(List<DaoParam> values) throws SQLException {
        int n = 0;
        for (DaoParam param : values) {
            parseParamValue(param);
            pstmt.setObject(++n, param.getColValue());
        }
    }

    private String getDbType(CommonDataSource dataSource) {
        String dbType = null;

        if (dataSource instanceof DynamicRoutingDataSource) {
            DynamicRoutingDataSource dynamicRoutingDataSource = (DynamicRoutingDataSource) dataSource;
            dataSource = dynamicRoutingDataSource.determineDataSource();
        }

        if ("com.p6spy.engine.spy.P6DataSource".equals(dataSource.getClass().getName())) {
            dataSource = (CommonDataSource) ReflectUtil.getValue(dataSource, "realDataSource");
        }

        if (dataSource instanceof DruidDataSource) {
            dbType = ((DruidDataSource) dataSource).getDbType();
        }
        return dbType;
    }

    private Map<String, String> getColumnsType(String tableName) throws SQLException {
        Map<String, String> columns = new HashMap();
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getColumns(conn.getCatalog(), null, tableName, null);
            while (rs.next()) {
                String colName = rs.getString("COLUMN_NAME");
                String colType = rs.getString("TYPE_NAME");
                columns.put(colName.toLowerCase(), colType);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
        return columns;
    }

    private void parseParamValue(DaoParam param) throws SQLException {
        if (this.tableName == null) {
            throw new RuntimeException("表名不能为空");
        }
        String colName = param.getColName().toLowerCase();
        Object colValue = param.getColValue();
        if (colValue == null) {
            return;
        }
        Map<String, String> columnsType = getColumnsType(this.tableName);
        if (columnsType == null || columnsType.size() == 0) {
            throw new RuntimeException("未找到字段类型,请检查表名是否正确");
        }
        String colType = columnsType.get(colName);
        if (colType == null) {
            throw new RuntimeException("未找到字段类型,请检查字段名是否正确");
        }
        if ("mysql".equals(dbType)) {
            if ("TIMESTAMP".equals(colType) || "DATETIME".equals(colType) || "DATE".equals(colType) || "TIME".equals(colType)) {
                DateTime javaDate = null;
                if (colValue instanceof String) {
                    javaDate = str2Date(String.valueOf(colValue));
                } else if (colValue instanceof DateTime) {
                    javaDate = new DateTime(colValue);
                } else {
                    throw new RuntimeException("不支持的日期类型");
                }
                if (javaDate == null) {
                    param.setColValue(null);
                    return;
                }
                if ("TIME".equals(colType)) {
                    Time time = new Time(javaDate.getMillis());
                    param.setColValue(time);
                } else {
                    Timestamp timestamp = new Timestamp(javaDate.getMillis());
                    param.setColValue(timestamp);
                }
            }
        } else if ("oracle".equals(dbType)) {
            if (colType.startsWith("TIMESTAMP") || colType.startsWith("DATE")) {
                DateTime javaDate = null;
                if (colValue instanceof String) {
                    javaDate = str2Date(String.valueOf(colValue));
                } else if (colValue instanceof DateTime) {
                    javaDate = new DateTime(colValue);
                } else {
                    throw new RuntimeException("不支持的日期类型");
                }
                if (javaDate == null) {
                    param.setColValue(null);
                    return;
                }
                Timestamp timestamp = new Timestamp(javaDate.getMillis());
                param.setColValue(timestamp);
            }
        }
    }

    private DateTime str2Date(String strDate) {
        if (StringUtils.isBlank(strDate)) {
            return null;
        }
        DateTime javaDate = null;
        if (strDate.length() == 10) {
            javaDate = DateTime.parse(strDate, DateTimeFormat.forPattern("yyyy-MM-dd"));
        } else if (strDate.length() == 19) {
            javaDate = DateTime.parse(strDate, DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        } else if (strDate.length() == 8) {
            javaDate = DateTime.parse(strDate, DateTimeFormat.forPattern("HH:mm:ss"));
        } else {
            throw new RuntimeException("不支持的日期格式!请输入yyyy-MM-dd HH:mm:ss或yyyy-MM-dd格式");
        }
        return javaDate;
    }

    @SneakyThrows
    private void setAutoCommit(boolean autoCommit) {
        conn.setAutoCommit(autoCommit);
    }

//    private void commit() {
//        try {
//            conn.commit();
//        } catch (SQLException e) {
//            logger.error(e.getMessage());
//        }
//    }
//
//    private void rollback() {
//        try {
//            conn.rollback();
//        } catch (SQLException e) {
//            logger.error(e.getMessage());
//        }
//    }

    private void closeAll() {
        // 关闭结果集对象
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("error", e);
            }
        }
        // 关闭PreparedStatement对象
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                log.error("error", e);
            }
        }
        // 关闭Connection 对象
//        if (conn != null) {
//            try {
//                conn.close();
//            } catch (SQLException e) {
//                logger.error(e.getMessage());
//            }
//        }
    }
}
